if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAgreementSummaries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAgreementSummaries]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

--------------------------------------------------------------------
--Modifications:
----------------
--08-28-2007	Performance improvements: introduce (nolock)
--------------------------------------------------------------------
--11-16-2007	Implemented "N/A" in place of Blank PI First and Last names. Trackit # 7319.
--07-02-2009    Tkt-9704-Modify the Project and Budget Start and End Date Search Parameters
--------------------------------------------------------------------
CREATE procedure [dbo].[GetAgreementSummaries](
	 @SessionId varchar(100),
	 @domainUserID				INTEGER					--1)
	,@includeProgramProjects	BIT				= null	--2)
	,@fundNumber				VARCHAR(8)		= null	--3)
	,@folderNumber				VARCHAR(16)		= null	--4)
	,@bicsNumber				VARCHAR(10)		= null	--5)
	,@walkerNumber				VARCHAR(10)		= null	--6)
	,@sponsorNumber				VARCHAR(32)		= null	--7)
	,@sponsorName				VARCHAR(64)		= null	--8)
	,@sponsorTypeId				INTEGER			= null	--9)
	,@agreementTypeId			INTEGER			= null	--10)
	,@activityCode				VARCHAR(4)		= null	--11)
	,@locationId				INTEGER			= null	--12)
	,@budgetStartDate			DATETIME		= null	--13)
	,@budgetEndDate				DATETIME		= null	--14)
	,@budgetEndFrom				DATETIME		= null	
	,@budgetEndTo				DATETIME		= null	
	,@projectStartDate			DATETIME		= null	--15)
	,@projectEndDate			DATETIME		= null	--16)
	,@projectEndFrom			DATETIME		= null	
	,@projectEndTo				DATETIME		= null	
	,@projectTitle				varchar(200) = null
	,@piName					VARCHAR(195)	= null	--17)
	,@chiefCodeList				VARCHAR(1000)	= null	--18)
	,@sponsorGroupTypeId		INTEGER			= null	--19)
	,@budgetEndingDays			INTEGER			= null	--20)
	,@projectEndingDays			INTEGER			= null	--21)
	,@daysFundCreated			INTEGER			= null	--22)
	,@lowBalance				DECIMAL(17,2)	= null	--23)
	,@highBalance				DECIMAL(17,2)	= null	--24)
	,@agreementStatusTypeId		VARCHAR(50)		= null	--25)
	,@fundStatusTypeId			INTEGER			= null	--26)
	,@viewTypeID int = null   --Added to filter on subcontract and "expense with budget"
)
AS
BEGIN
	SET NOCOUNT ON

	SET	@includeProgramProjects	= ISNULL(@includeProgramProjects, 0)
	SET	@fundNumber				= ISNULL(@fundNumber, '')
	SET	@folderNumber			= ISNULL(@folderNumber, '')
	SET	@bicsNumber				= ISNULL(@bicsNumber, '')
	SET	@walkerNumber			= ISNULL(@walkerNumber, '')
	SET	@sponsorNumber			= ISNULL(@sponsorNumber, '')
	SET	@sponsorName			= ISNULL(@sponsorName, '')
	SET	@sponsorTypeId			= ISNULL(@sponsorTypeId, 0)
	SET	@agreementTypeId		= ISNULL(@agreementTypeId, 0)
	SET	@activityCode			= ISNULL(@activityCode, '')
	SET	@locationId				= ISNULL(@locationId, 0)
	SET	@budgetStartDate		= ISNULL(@budgetStartDate, '')
	SET	@budgetEndDate			= ISNULL(@budgetEndDate, '')
	SET	@budgetEndFrom			= ISNULL(@budgetEndFrom, '')
	SET	@budgetEndTo			= ISNULL(@budgetEndTo, '')
	SET	@projectStartDate		= ISNULL(@projectStartDate, '')
	SET	@projectEndDate			= ISNULL(@projectEndDate, '') 
	SET	@projectEndFrom			= ISNULL(@projectEndFrom, '')
	SET	@projectEndTo			= ISNULL(@projectEndTo, '') 
	SET	@projectTitle			= ISNULL(@projectTitle, '') 
	SET	@piName					= ISNULL(@piName, '')
	SET	@chiefCodeList			= ISNULL(@chiefCodeList, '')
	SET	@sponsorGroupTypeId		= ISNULL(@sponsorGroupTypeId, 0)
	SET	@budgetEndingDays		= ISNULL(@budgetEndingDays, 0)
	SET	@projectEndingDays		= ISNULL(@projectEndingDays, 0)
	SET	@daysFundCreated		= ISNULL(@daysFundCreated, 0)
	SET	@lowBalance				= ISNULL(@lowBalance, -922337203685477)
	SET	@highBalance			= ISNULL(@highBalance, 922337203685477)
	SET	@fundStatusTypeId		= ISNULL(@fundStatusTypeId, 0)

	DECLARE @today DATETIME
	SET @today = GetDate()

	SET @today = CAST(DATEPART(month,@today) AS VARCHAR)
				+ '/' + CAST(DATEPART(day,@today) AS VARCHAR)
				+ '/' + CAST(DATEPART(year,@today) AS VARCHAR)

	DECLARE @budgetEndingInDate				DATETIME
	DECLARE @agreementResourceTypeID		CHAR
	DECLARE @primarySponsorRelationTypeId	CHAR
	DECLARE @viewFinancialInfoBitValue		INTEGER
	DECLARE @viewPendingAgreementsBitValue	INTEGER
	DECLARE @authorizedRightsBitMask		INTEGER
	DECLARE @pendingAgreementStatusTypeID	CHAR

	SET @budgetEndingInDate				= DATEADD(day,@budgetEndingDays,@today);
	SET @agreementResourceTypeID		= 1
	SET @primarySponsorRelationTypeId	= 1
	SET @viewFinancialInfoBitValue		= 4
	SET @viewPendingAgreementsBitValue	= 32
	SET @authorizedRightsBitMask		= @viewFinancialInfoBitValue + @viewPendingAgreementsBitValue
	SET @pendingAgreementStatusTypeID	= 1

	/*
		Only agreement resources that the user is authorized for are returned.
		This is accomplised by joining to the fnAdmQGetAuthorizedResourceList udf
		which holds the logic for returning distinct list of resource id's and user's
		composite access rights for each resource based on direct,org, lab, and portfolio
		access rights.  Further, the where clause enforces that an agreement resource
		in a pending status is only returned if the user has the view pending agreement
		access level for that agreement resource.
	*/

	SELECT
		  agf.AgreementID
		, ISNULL(ait.AccessLevel,0) AS AccessLevel
		, aty.Name AS AgreementType
		, ISNULL(agf.PSActivityDescription, '') AS ActivityType
		, st.Name AS StatusType
		, ISNULL(agf.FundNumber, '') AS FundNumber
		, ISNULL(agf.ShortTitle, 'No Title') AS ShortTitle
		, CASE datalength(agf.Title)
			WHEN 1 THEN 'No Title'	-- The datalength of a text field is 1 when it is empty!
			ELSE agf.Title
		  END AS Title
		, agf.ProjectStartDate
		, agf.ProjectEndDate
		, agf.BudgetStartDate AS BudgetStartDate
		, agf.BudgetEndDate AS BudgetEndDate
		, ISNULL(agf.SponsorName, '') AS SponsorName
		, 
		case isnull(agf.PILastName,'') + isnull(agf.PIFirstName,'') when '' then 'N/A'
		else 
		LTRIM(RTRIM(ISNULL(agf.PILastName, ''))) + ', ' + LTRIM(RTRIM(ISNULL(agf.PIFirstName, ''))) + CASE WHEN agf.PIMIddleName is null THEN '' ELSE ' ' + LTRIM(RTRIM(agf.PIMIddleName)) END 
		end AS PIName
		--everything selected below is for dataview filtering
		, ISNULL(agf.FolderNumber,'') AS FolderNumber
		, ISNULL(agf.BICSNumber,'') AS BICSNumber
		, ISNULL(agf.WalkerNumber,'') AS WalkerNumber
		, ISNULL(agf.SponsorNumber,'') AS SponsorNumber
		, ISNULL(agf.SponsorTypeId,0) AS SponsorTypeId
		, agf.AgrAgreementTypeId AS AgreementTypeId
		, ISNULL(agf.PSActivityCode, '') AS ActivityCode
		, ISNULL(agf.FacilityId,0) AS FacilityId
		, ISNULL(agf.PrincipalInvestigatorId, 0) AS PIAdmPersonId
		, agf.OrganizationId AS OrganizationId
		, ISNULL(agf.SponsorGroupTypeId,0) AS SponsorGroupTypeId
		--@@@TODO need to get low balance
		--@@@TODO need to get high balance
		, agf.StatusTypeId AS AgreementStatusTypeId
	FROM
		dbo.AgrAgreement_Fct agf (nolock)
		INNER JOIN
			dbo.[FilterAgreements](	 @domainUserId
									,@authorizedRightsBitMask
									,@includeProgramProjects
									,@fundNumber
									,@folderNumber
									,@bicsNumber
									,@walkerNumber
									,@sponsorNumber
									,@sponsorName
									,@sponsorTypeId
									,@agreementTypeId
									,@activityCode
									,@locationId
									,@budgetStartDate
									,@budgetEndDate
									,@budgetEndFrom
									,@budgetEndTo
									,@projectStartDate
									,@projectEndDate
									,@projectEndFrom
									,@projectEndTo
									,@projectTitle
									,@piName
									,@chiefCodeList
									,@sponsorGroupTypeId
									,@budgetEndingDays
									,@projectEndingDays
									,@daysFundCreated
									,@lowBalance
									,@highBalance
									,@agreementStatusTypeId
									,@fundStatusTypeId
									,@today) ait
		ON
			ait.AgreementId = agf.AgreementId
	INNER JOIN dbo.AgrAgreementType aty (nolock) on agf.AgrAgreementTypeId = aty.[Id]
	INNER JOIN dbo.AgrAgreementStatusType st (nolock) on st.[Id] = agf.StatusTypeId
	WHERE ((@budgetStartDate = '') or (IsNull(agf.BudgetStartDate,'1/1/1900') between @budgetStartDate and @budgetEndDate ))
		   and ((@budgetEndFrom = '') or (isnull(agf.BudgetEndDate,'1/1/1900') between @budgetEndFrom and @budgetEndTo))and
	 	   ISNULL(agf.BudgetStartDate,'01/01/1900') = 
			(
				SELECT	max(ISNULL(agf2.BudgetStartDate,'01/01/1900'))
				FROM	dbo.AgrAgreement_Fct agf2 (nolock)
				WHERE	agf2.FolderNumber = agf.FolderNumber
				
			)
	order by
		agf.ProjectEndDate

	-- Need to update the scratch table so visits to the Overview Pages from
	-- Agreement Summary also includes filter criteria
	-- Generate a table of Funds (identified by the respective ID) that the user has access to, 
	-- based on the specified criteria.
	-- This will be used to limit the final resultset.

	IF OBJECT_ID('tempdb..#TempFundCollection') is not null drop table #TempFundCollection
	create table #TempFundCollection (FundID int not null, AccessLevel int not null)

	exec FilterFundsToTable @SessionId, @domainUserId, @authorizedRightsBitMask, @includeProgramProjects, 
						@fundNumber, @folderNumber, @bicsNumber, @walkerNumber, @sponsorNumber, @sponsorName, 
						@sponsorTypeId, @agreementTypeId, @activityCode, @locationId, @budgetStartDate, 
						@budgetEndDate,@budgetEndFrom,@budgetEndTo,@projectStartDate, @projectEndDate,@projectEndFrom,@projectEndTo,@projectTitle,@piName, @chiefCodeList, 
						@sponsorGroupTypeId, @budgetEndingDays/*@daysBudgetEnding*/, 
						@projectEndingDays/*@daysProjectEnding*/, @daysFundCreated, @lowBalance, @highBalance,
						@agreementStatusTypeId	/*@agreementStatusTypeList*/, @fundStatusTypeId	
end








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
